﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;

// <copyright file="SMOperator" company="42ENG">
// Copyright (c) 2014 All Rights Reserved
// <author>Dennis Johnston</author>
// </copyright> 
namespace SMOperator
{
    /// <summary>
    /// Defines the object types
    /// </summary>
    public enum SqlObjectType
    {
        None = 0,
        Schema = 1,
        Table = 2,
        Synonym = 3,
        UserDefinedFunction = 4,
        View = 5,
        StoredProcedure = 6,
        ForeignKey = 7,
        SetupScript = 8,
        UserTypeTable = 9,
        UserType = 10,
        Index = 11,
        ClusteredIndex = 12,
        Trigger = 13
    }
	/// <summary>
	/// Craplet responsible for serializing/deserializing from a db catalog
	/// </summary>
	public class SMOperator : IDisposable
	{
		protected TextWriterTraceListener Listener
		{ get; set; }
		protected TextWriterTraceListener ConsoleListener
		{ get; set; }
        /// <summary>
        /// Entry point
        /// </summary>
		public SMOperator()
		{
			string fileName = string.Format("SMOpLog_{0}.txt", DateTime.Now.ToString("yyyyMMdd-hhmmss"));
			fileName = Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetModules().First().FullyQualifiedName), fileName);

			ConsoleListener = new TextWriterTraceListener(Console.Out);

			try
			{
				Listener = new System.Diagnostics.TextWriterTraceListener(System.IO.File.CreateText(fileName));
				System.Diagnostics.Trace.Listeners.Add(Listener);
			}
			catch { }
			
			System.Diagnostics.Trace.Listeners.Add(ConsoleListener);
			System.Diagnostics.Trace.AutoFlush = true;
		}
        public void Dispose()
        {
            if (Listener != null && System.Diagnostics.Trace.Listeners.Contains(Listener))
                System.Diagnostics.Trace.Listeners.Remove(Listener);
            if (ConsoleListener != null && System.Diagnostics.Trace.Listeners.Contains(ConsoleListener))
                System.Diagnostics.Trace.Listeners.Remove(ConsoleListener);
        }
		/// <summary>
		/// Serializes a catalog into SMO object files
		/// </summary>
		/// <param name="keyName"></param>
		/// <param name="catalogLogicalName"></param>
		public int ScriptFromCatalog(string keyName, string catalogLogicalName)
		{
			//global config, get connection by key
			CatalogContext catalog = new CatalogContext();
            List<string> exceptions = new List<string>();

			//sanity
			if (!catalog.IsConfigExist)
			{
				Trace.WriteLine("Missing App.Config, exiting, exiting"); Trace.Flush();
				return (int)ReturnCode.FailDueSetup;
			}
			if (string.IsNullOrEmpty(catalog.GetConnectionString(keyName)))
			{
				Trace.WriteLine("Invalid app.config keyname, exiting"); Trace.Flush();
				return (int)ReturnCode.FailDueSetup; ;
			}

			//connection
            string sqlString = catalog.GetConnectionString(keyName);
			SqlConnection sqlConnection = new SqlConnection(sqlString);

			try { sqlConnection.Open(); }
			catch (SqlException)
			{
				Trace.WriteLine(string.Format(@"Database doesn't exist OR Current user {0}\{1} does not have SQL SERVER permission", Environment.UserDomainName, Environment.UserName)); Trace.Flush();
				return (int)ReturnCode.FailDueSetup;
			}                      

			//smo server/database
			Server server = new Server(sqlConnection.DataSource);
			Database db = new Database(server, sqlConnection.Database);

			//default scripting options
			Scripter scripterEngine = new Scripter(server);
            
            if (keyName.Contains("ImportantDatabaseName"))
                db.Refresh();
            else
            {
                Trace.WriteLine("Discovering database contents... please be patient");
                db.Discover();
            }

            #region schemas synonyms
            foreach (Schema sch in db.Schemas)
			{
                if (sch.Name == "test" || (sch.Name == "dbo") || sch.Name.Contains("db_"))
					continue;
                if (sch.IsSystemObject)
                    continue;

                StringCollection schScripts = ScriptObject(sch, GetScriptingOptions(false, true, true, false));
                string file = SerializeToFile(schScripts, catalogLogicalName, string.Empty, sch.Name, SqlObjectType.Schema);
                Trace.WriteLine(string.Format("Object {0} scripted to: {1}", sch, System.IO.Path.GetFileName(file)));
			}
			foreach (Synonym syn in GetSynonyms(sqlConnection, db))
			{
				if (syn.Schema == "test" || syn.Schema == "dbo")
					continue;

                StringCollection synScripts = ScriptObject(syn, GetScriptingOptions(false, true, true, false));
                synScripts = ScriptObjectRemove(synScripts, string.Format("[{0}].", server.Name));
                string file = SerializeToFile(synScripts, catalogLogicalName, syn.Schema, syn.Name, SqlObjectType.Synonym);
				Trace.WriteLine(string.Format("Object {0} scripted to: {1}", syn, System.IO.Path.GetFileName(file)));
            }
            #endregion
            #region tables
			foreach (Table tbl in db.Tables)
			{
				if (tbl.Schema == "test" || (tbl.Schema == "dbo" && !keyName.Contains("Landing")))
					continue;
                if (tbl.IsSystemObject || tbl.Schema == "dbo" && !keyName.Contains("Landing"))
                    continue;

                StringCollection tableScripts = ScriptObject(tbl, GetScriptingOptions(false, true, false, false));
				string file = SerializeToFile(tableScripts, catalogLogicalName, tbl.Schema, tbl.Name, SqlObjectType.Table);
				Trace.WriteLine(string.Format("Object {0} scripted to: {1}", tbl, System.IO.Path.GetFileName(file)));

				if (tbl.ForeignKeys.Count > 0)
				{
					foreach (ForeignKey fk in tbl.ForeignKeys)
					{
                        StringCollection tableFKScript = ScriptObject(fk, GetScriptingOptions(true, false, false, false));
						string fkfile = SerializeToFile(tableFKScript, catalogLogicalName, string.Empty, fk.Name, SqlObjectType.ForeignKey);
						Trace.WriteLine(string.Format("Object {0} scripted to: {1}", fk, System.IO.Path.GetFileName(fkfile)));
					}
				}
                if (tbl.Indexes.Count > 0)
                {
                    foreach (Index idx in tbl.Indexes)
                    {
                        StringCollection tableIXScript = ScriptObject(idx, GetScriptingOptions(true, true, !idx.IsClustered, idx.IsClustered));
                        tableIXScript.Insert(0, "SET ANSI_PADDING ON");
                        string idxfile = SerializeToFile(tableIXScript, catalogLogicalName, !idx.IsClustered && (idx.IndexKeyType != IndexKeyType.DriPrimaryKey) ? "TBL" : string.Empty, idx.Name, (idx.IsClustered || (idx.IndexKeyType == IndexKeyType.DriPrimaryKey)) ? SqlObjectType.ClusteredIndex : SqlObjectType.Index);
                        Trace.WriteLine(string.Format("Object {0} (IDX) scripted to: {1}", idx, System.IO.Path.GetFileName(idxfile)));
                    }
                    foreach (Trigger trig in tbl.Triggers)
                    {
                        StringCollection triggerScript = ScriptObject(trig, GetScriptingOptions(true, true, false, false));
                        string trigFile = SerializeToFile(triggerScript, catalogLogicalName, string.Empty, trig.Name, SqlObjectType.Trigger);
                        Trace.WriteLine(string.Format("Object {0} (TRIGGER) scripted to: {1}", trig, System.IO.Path.GetFileName(trigFile)));
                    }
                }
            }
            #endregion
            #region views
			IList<View> viewsScripted = new List<View>();
			foreach (View vw in db.Views)
			{
                if (vw.IsSystemObject || (vw.Schema == "dbo" && !keyName.Contains("Landing")) || vw.Schema == "test") continue;
                StringCollection viewScripts = ScriptObject(vw, GetScriptingOptions(false, false, true, false));
                string file = SerializeToFile(viewScripts, catalogLogicalName, vw.Schema, vw.Name, SqlObjectType.View);
				Trace.WriteLine(string.Format("Object {0} scripted to: {1}", vw, System.IO.Path.GetFileName(file)));
            }
            #endregion
            #region table types
			foreach (UserDefinedTableType udtt in db.UserDefinedTableTypes)
			{
                if ((udtt.Schema == "dbo" && !keyName.Contains("Landing")) || udtt.Schema == "test") continue;
                StringCollection udttScripts = ScriptObject(udtt, GetScriptingOptions(false, false, true, false));
                string file = SerializeToFile(udttScripts, catalogLogicalName, udtt.Schema, udtt.Name, SqlObjectType.UserTypeTable);
				Trace.WriteLine(string.Format("Object {0} scripted to: {1}", udtt, System.IO.Path.GetFileName(file)));
            }
            #endregion
            #region udf
			foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
			{
				if (udf.IsSystemObject || (udf.Schema == "dbo" && !keyName.Contains("Landing")) || udf.Schema == "test") continue;
                StringCollection udfScripts = ScriptObject(udf, GetScriptingOptions(false, false, true, false));
                string file = SerializeToFile(udfScripts, catalogLogicalName, udf.Schema, udf.Name, SqlObjectType.UserDefinedFunction);
				Trace.WriteLine(string.Format("Object {0} scripted to: {1}", udf, System.IO.Path.GetFileName(file)));
            }
            #endregion 
            #region sprocs
			foreach (StoredProcedure sproc in db.StoredProcedures)
			{
				if (sproc.IsSystemObject || (sproc.Schema == "dbo" && !keyName.Contains("Landing")) || sproc.Schema == "test") continue;
                StringCollection sprocScripts = ScriptObject(sproc, GetScriptingOptions(false, false, true, false));
                string file = SerializeToFile(sprocScripts, catalogLogicalName, sproc.Schema, sproc.Name, SqlObjectType.StoredProcedure);
				Trace.WriteLine(string.Format("Object {0} scripted to: {1}", sproc, System.IO.Path.GetFileName(file)));
            }
            #endregion

            Trace.Flush();

            ReportExceptions(exceptions);

			return exceptions.Count;
		}
		/// <summary>
		/// Removes a token from the StringCollection
		/// Might be useful for, say, removing a servername
		/// </summary>
		/// <param name="tableScripts"></param>
		/// <param name="token"></param>
		/// <returns></returns>
		private StringCollection ScriptObjectRemove(StringCollection tableScripts, string token)
		{
			for(int i = 0; i <= tableScripts.Count - 1; i++)
				if (tableScripts[i].Contains(token.ToUpper()))
					tableScripts[i] = tableScripts[i].Replace(token.ToUpper(), "");
			return tableScripts;
		}
		/// <summary>
		/// Deserializes SMO object files into a catalog
		/// </summary>
		/// <param name="keyName"></param>
		/// <param name="catalogLogicalName"></param>
		public int ScriptToCatalog(string keyName, string catalogLogicalName)
		{
			//global config, get connection by key
			CatalogContext catalog = new CatalogContext();
            List<string> exceptions = new List<string>();

			//sanity
			if (!catalog.IsConfigExist)
			{
				Trace.WriteLine("Missing App.Config, exiting"); Trace.Flush();
				return (int)ReturnCode.FailDueSetup;
			}

			if (string.IsNullOrEmpty(catalog.GetConnectionString(keyName)))
			{
				Trace.WriteLine("Invalid app.config keyname, exiting"); Trace.Flush();
				return (int)ReturnCode.FailDueSetup;
			}

			bool isError = false;
			string workingPath = GetScriptPath(catalogLogicalName, out isError);
			if (isError)
			{
				Trace.WriteLine("Logical path doesn't exist, exiting"); Trace.Flush();
				return (int)ReturnCode.FailDueSetup;
			}

			Trace.WriteLine("Opening connection..."); Trace.Flush();

			//connection
            using (SqlConnection sqlConnection = new SqlConnection(catalog.GetConnectionString(keyName)))
            {
                try { sqlConnection.Open(); }
                catch (SqlException)
                {
                    Trace.WriteLine(string.Format(@"Current user {0}\{1} does not have SQL SERVER permission", Environment.UserDomainName, Environment.UserName)); Trace.Flush();
                    return (int)ReturnCode.FailDueSetup;
                }

                Microsoft.SqlServer.Management.Common.ServerConnection conn = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
                conn.StatementTimeout = Microsoft.SqlServer.Management.Common.ServerConnection.NoConnectionTimeout;

                //smo server/database
                Server server = new Server(conn);
                Database db = new Database(server, sqlConnection.Database);
                
                Trace.WriteLine(string.Format("...Opened to {0}.{1}", sqlConnection.DataSource, sqlConnection.Database)); Trace.Flush();

                #region Script Execution
                
                Trace.Write("Discovering database contents...");

                //large database, just do a forward firehose instead of caching all objects
                if (keyName.Contains("BigDatabaseName"))
                    db.Refresh();
                else
                {
                    object o = db.Discover();
                }

                Trace.Write("Dropping all database content..."); Trace.Flush();

                //clear contents, skip tables if critical
                if (!keyName.Contains("ImportantDatabaseName"))
                    DropCatalogObjects(sqlConnection, db, false);

                Trace.WriteLine("...Done");

                Trace.WriteLine(string.Format("Scripting from path {0}", workingPath));

                List<SqlFilePayload> tablesToExecute = GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.Table);
                List<SqlFilePayload> viewsToExecute = GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.View);
                List<SqlFilePayload> tablesToReExecute = new List<SqlFilePayload>();

                LinkedList<SqlFilePayload> orderedItemsToReExecute = new LinkedList<SqlFilePayload>();
                LinkedList<SqlFilePayload> orderedItems2ToReExecute = new LinkedList<SqlFilePayload>();

                Trace.WriteLine("...Done");
                //rebuild contents
                if (keyName.Contains("Mapping"))
                {
                    #region setup
                    foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.SetupScript))
                    {
                        try
                        {
                            script.CleanUp();
                            db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName));
                        }
                        catch (Exception ex)
                        {
                            string msg = string.Format("Failed SetupScript script {0}, {1}", script.FileName, GetErrorMessage(ex));
                            Trace.WriteLine(msg);
                            exceptions.Add(msg);
                        }
                    }
                #endregion
                }
                #region schema synonym
                foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.Schema))
                {
                    if (db.Schemas[script.SchemaName] != null)
                        continue;
                    try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName)); }
                    catch (Exception ex) 
                    {
                        string msg = string.Format("Failed Schema script {0}, {1}", script.FileName, GetErrorMessage(ex));
                        Trace.WriteLine(msg); 
                        exceptions.Add(msg); 
                    }
                }
                foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.Synonym))
                {
                    try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName)); }
                    catch (Exception ex)
                    {
                        string msg = string.Format("Failed Synonym script {0}, {1}", script.FileName, GetErrorMessage(ex));
                        Trace.WriteLine(msg);
                        exceptions.Add(msg);
                    }
                }
                #endregion
                #region tables
                foreach (SqlFilePayload script in tablesToExecute)
                {
                    if (string.IsNullOrEmpty(script.TableName))
                    {
                        Trace.WriteLine(string.Format("Skipping script: {0}"), script.FileName);
                        continue;
                    }
                    if (db.Tables[script.TableName, script.SchemaName] != null)
                    {
                        Trace.WriteLine(string.Format("Object already exists for criticalDb, skipping: {0}", script.ObjectName));
                        continue;
                    }
                    try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName)); }
                    catch (Exception ex)
                    {
                        if (AssertBadOrder(ex, SqlObjectType.Table))
                        {
                            tablesToReExecute.Add(script);
                        }
                        else
                        {
                            string msg = string.Format("Failed Table script {0}, {1}", script.FileName, GetErrorMessage(ex));
                            Trace.WriteLine(msg);
                            exceptions.Add(msg);
                        }
                    }
                }
                foreach (SqlFilePayload script in tablesToReExecute.OrderBy(v => v.ObjectRank).ThenByDescending(v => v.FileName))
                {
                    try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName)); }
                    catch (Exception ex)
                    {
                        string msg = string.Format("Failed Table script {0}, {1}", script.FileName, GetErrorMessage(ex));
                        Trace.WriteLine(msg);
                        exceptions.Add(msg);
                    }
                }
                #endregion
                #region clusteredindex                
                foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.ClusteredIndex))
                {
                    try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName)); }
                    catch (Exception ex)
                    {
                        string msg = string.Format("Failed ClusteredIndex script {0}, {1}", script.FileName, GetErrorMessage(ex));
                        Trace.WriteLine(msg);
                        exceptions.Add(msg);
                    }
                }
                #endregion
                #region udf
                foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.UserDefinedFunction))
                {
                    try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName)); }
                    catch (Exception ex)
                    {
                        if (AssertBadOrder(ex, SqlObjectType.UserDefinedFunction))
                        {
                            AddToReExecute(db, workingPath, SqlObjectType.UserDefinedFunction, orderedItemsToReExecute, script, ex);
                        }
                        else
                        {
                            string msg = string.Format("Failed UserDefinedFunction script {0}, {1}", script.FileName, GetErrorMessage(ex));
                            Trace.WriteLine(msg);
                            exceptions.Add(msg);
                        }
                    }
                }
                #endregion
                #region views
                foreach (SqlFilePayload script in viewsToExecute.OrderBy(v => v.ObjectRank).ThenBy(v => v.FileName))
                {
                    try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName)); }
                    catch (Exception ex)
                    {
                        if (AssertBadOrder(ex, SqlObjectType.View))
                        {
                            AddToReExecute(db, workingPath, SqlObjectType.View, orderedItemsToReExecute, script, ex);
                        }
                        else
                        {
                            string msg = string.Format("Failed View script {0}, {1}", script.FileName, GetErrorMessage(ex));
                            Trace.WriteLine(msg);
                            exceptions.Add(msg);
                        }

                    }
                }
                #endregion
                #region ordered views
                foreach (SqlFilePayload script in orderedItemsToReExecute)
                {
                    try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("ReExecuting: {0}", script.FileName)); }
                    catch (Exception ex)
                    {
                        if (AssertBadOrder(ex, SqlObjectType.View))
                        {
                            AddToReExecute(db, workingPath, SqlObjectType.View, orderedItems2ToReExecute, script, ex);
                        }
                        else
                        {
                            string msg = string.Format("Failed View script {0}, {1}", script.FileName, GetErrorMessage(ex));
                            Trace.WriteLine(msg);
                            exceptions.Add(msg);
                        }
                    }
                }
                orderedItems2ToReExecute.OrderBy(p => p.FileName);
                foreach (SqlFilePayload script in orderedItems2ToReExecute)
                {
                    try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("ReExecuting: {0}", script.FileName)); }
                    catch (Exception ex)
                    {
                        string msg = string.Format("Failed to re-execute View script {0}, {1}", script.FileName, GetErrorMessage(ex));
                        Trace.WriteLine(msg);
                        exceptions.Add(msg);
                    }
                }
                #endregion
                #region typetable
                foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.UserTypeTable))
                {
                    try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName)); }
                    catch (Exception ex)
                    {
                        string msg = string.Format("Failed UserTypeTable script {0}, {1}", script.FileName, GetErrorMessage(ex));
                        Trace.WriteLine(msg);
                        exceptions.Add(msg);
                    }
                }
                #endregion
                #region sprocs
                foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.StoredProcedure))
                {
                    try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName)); }
                    catch (Exception ex)
                    {
                        string msg = string.Format("Failed StoredProcedure script {0}, {1}", script.FileName, GetErrorMessage(ex));
                        Trace.WriteLine(msg);
                        exceptions.Add(msg);
                    }
                }
                #endregion
                #region fk
                foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.ForeignKey))
                {
                    try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName)); }
                    catch (Exception ex)
                    {
                        string msg = string.Format("Failed ForeignKey script {0}, {1}", script.FileName, GetErrorMessage(ex));
                        Trace.WriteLine(msg);
                        exceptions.Add(msg);
                    }
                }
                #endregion
                #region trigger
                foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.Trigger))
                {
                    try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName)); }
                    catch (Exception ex)
                    {
                        string msg = string.Format("Failed Trigger script {0}, {1}", script.FileName, GetErrorMessage(ex));
                        Trace.WriteLine(msg);
                        exceptions.Add(msg);
                    }
                }
                #endregion
                #region setup
                if (!keyName.Contains("Mapping"))
                {
                    foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.SetupScript))
                    {
                        try
                        {
                            script.CleanUp();
                            db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName));
                        }
                        catch (Exception ex)
                        {
                            string msg = string.Format("Failed SetupScript script {0}, {1}", script.FileName, GetErrorMessage(ex));
                            Trace.WriteLine(msg);
                            exceptions.Add(msg);
                        }
                    }
                }
                #endregion

                #endregion
            }

			Trace.Flush();

            ReportExceptions(exceptions);

			return exceptions.Count;
		}
        /// <summary>
        /// Barks about exceptions
        /// </summary>
        /// <param name="exceptions"></param>
        private static void ReportExceptions(List<string> exceptions)
        {
            if (exceptions.Any())
            {
                Trace.WriteLine("");
                Trace.WriteLine(string.Format("Summary of {0} errors:", exceptions.Count));
                foreach (string ex in exceptions)
                {
                    string msg = string.Format("    {0}", ex);
                    Trace.WriteLine(msg);
                }
                Trace.WriteLine("");
            }
        }
        /// <summary>
        /// Will add/remove indexes into a catalog for which a index script exists
        /// </summary>
        /// <param name="keyName"></param>
        /// <param name="catalogLogicalName"></param>
        /// <returns></returns>
        public int IndexCatalog(string keyName, string catalogLogicalName, bool isAddingIndex, bool isAllNCIndexes)
        {
            //global config, get connection by key
            CatalogContext catalog = new CatalogContext();
            int exceptionCount = 0;
            List<string> exceptions = new List<string>();

            //sanity
            if (!catalog.IsConfigExist)
            {
                Trace.WriteLine("Missing App.Config, exiting"); Trace.Flush();
                return (int)ReturnCode.FailDueSetup;
            }

            if (string.IsNullOrEmpty(catalog.GetConnectionString(keyName)))
            {
                Trace.WriteLine("Invalid app.config keyname, exiting"); Trace.Flush();
                return (int)ReturnCode.FailDueSetup;
            }

            bool isError = false;

            //set the default working path
            string workingPath = GetScriptPath(catalogLogicalName, out isError);
            if (isError)
            {
                Trace.WriteLine("Logical path doesn't exist, exiting"); Trace.Flush();
                return (int)ReturnCode.FailDueSetup;
            }

            Trace.WriteLine("Opening connection..."); Trace.Flush();

            //connection
            using (SqlConnection sqlConnection = new SqlConnection(catalog.GetConnectionString(keyName)))
            {
                try { sqlConnection.Open(); }
                catch (SqlException)
                {
                    Trace.WriteLine(string.Format(@"Current user {0}\{1} does not have SQL SERVER permission", Environment.UserDomainName, Environment.UserName)); Trace.Flush();
                    return (int)ReturnCode.FailDueSetup;
                }

                //smo server/database
                Server server = new Server(sqlConnection.DataSource);
                Database db = new Database(server, sqlConnection.Database);

                Trace.Write("Discovering database contents...");

                //large database, just do a forward firehose instead of caching all objects
                if (keyName == "ImportantDatabaseName")
                    db.Refresh();
                else
                {
                    object o = db.Discover();
                }
                Trace.WriteLine("...Done");
                                
                #region process specific indexes
                if (!isAllNCIndexes)
                {
                    List<SqlFilePayload> nonClusteredIndex = GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.Index);
                    List<SqlFilePayload> clusteredIndex = GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.ClusteredIndex);
                    Comparison<SqlFilePayload> comp = (x,y) => x.NameRank.CompareTo(y.NameRank);
                    nonClusteredIndex.Sort(comp);
                    
                    List<SqlFilePayload> items = new List<SqlFilePayload>();
                    
                    if (isAddingIndex)
                    {
                        Trace.WriteLine("Adding Indexes:");
                        items.AddRange(clusteredIndex);
                        items.AddRange(nonClusteredIndex);
                    }
                    else
                    { 
                        Trace.WriteLine("Dropping Indexes:");
                        items.AddRange(nonClusteredIndex);
                    }

                    #region actions for non clustered indexes
                    foreach (SqlFilePayload script in items)
                    {
                        if (!string.IsNullOrEmpty(script.ObjectName) && !string.IsNullOrEmpty(script.SchemaName))
                        {
                            //if adding the index
                            if (isAddingIndex)
                            {
                                try { db.ExecuteNonQuery(script.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", script.FileName)); }
                                catch (Exception ex) { Trace.WriteLine(string.Format("Failed Index script {0}, {1}", script.FileName, GetErrorMessage(ex))); exceptionCount++; }
                            }
                            //otherwise drop the index
                            else
                            {
                                try { DropIndex(db, script.SchemaName, script.TableName, script.ConstraintName); }
                                catch (Exception ex) { Trace.WriteLine(string.Format("Failed removing Index {0}, {1}", script.FileName, GetErrorMessage(ex))); exceptionCount++; }
                            }
                        }
                    }
                    #endregion
                }
                #endregion
                #region drop all non-clustered indexes
                else
                {
                    for (int i = 0; i <= db.Tables.Count - 1; i++)
                    {
                        Table tbl = db.Tables[i];
                        if (tbl.IsSystemObject)
                            continue;
                            
                        for (int j = 0; j < tbl.Indexes.Count - 1; j++)
                        {
                            Index idx = tbl.Indexes[j];
                            //not a system table, not a clustered index, and is not a unique constraint
                            if (!idx.IsClustered && !idx.IsSystemObject && !idx.IsUnique)
                            {
                                DropIndex(db, tbl.Schema, tbl.Name, idx.Name);
                            }
                        }
                    }
                }
                #endregion
            }
            
            Trace.Flush();

            ReportExceptions(exceptions);

            return exceptionCount;
        }
        /// <summary>
        /// Drop an index on the specified table
        /// </summary>
        /// <param name="db"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="constraintName"></param>
        private void DropIndex(Database db, string schemaName, string tableName, string constraintName)
        {
            Trace.WriteLine(string.Format("Dropping Index {2} on {0}.{1}", schemaName, tableName, constraintName));

            Table tbl = db.Tables[tableName, schemaName];
            if (tbl != null)
            {
                for (int i = 0; i <= tbl.Indexes.Count - 1; i++)
                {
                    Index idx = tbl.Indexes[i];
                    if (idx != null && idx.Name == constraintName)
                    {
                        idx.Drop();
                    }
                }
            }
        }
		/// <   summary>
		/// Reexecutes and adds to a queue
		/// </summary>
		/// <param name="db"></param>
		/// <param name="workingPath"></param>
		/// <param name="objType"></param>
		/// <param name="orderedItemsToReExecute"></param>
		/// <param name="script"></param>
		/// <param name="ex"></param>
		private static void AddToReExecute(Database db, string workingPath, SqlObjectType objType, LinkedList<SqlFilePayload> orderedItemsToReExecute, SqlFilePayload script, Exception ex)
		{
			//get the name of the offending object
			string depObjectName = AssertBadObjectName(ex, objType);
			if (!string.IsNullOrEmpty(depObjectName))
			{
				//determine if the parent exists in the list
				var parent = orderedItemsToReExecute.Where(p => p.ObjectName == depObjectName).FirstOrDefault();
				if (parent == null)
				{
					//if it doesn't find it
					SqlFilePayload parentScript = GetScriptFromPath(workingPath, depObjectName);
					if (parentScript != null)
					{
						try { db.ExecuteNonQuery(parentScript.SqlScript); Trace.WriteLine(string.Format("Executing: {0}", parentScript.FileName)); }
						catch (Exception) { }

						orderedItemsToReExecute.AddLast(parentScript);
					}
				}
			}
			orderedItemsToReExecute.AddLast(script);
		}
		/// <summary>
		/// Determines from an Exception if something failed due to ordering
		/// </summary>
		/// <param name="exceptionString"></param>
		/// <returns></returns>
		private bool AssertBadOrder(Exception ex, SqlObjectType type)
		{
			string errorMessage = GetErrorMessage(ex);
			if(string.IsNullOrEmpty(errorMessage))
				return false;
			if (type == SqlObjectType.Table)
			{
				if (errorMessage.Contains("because it does not exist or you do not have permission"))
					return true;
			}
			if (type == SqlObjectType.View || type == SqlObjectType.UserDefinedFunction)
			{
				if (errorMessage.Contains("Invalid object name '"))
					return true;
			}

			return false;
		}
		/// <summary>
		/// Determines the name of the object that caused a failure
		/// </summary>
		/// <param name="ex"></param>
		/// <param name="type"></param>
		/// <returns></returns>
		internal static string AssertBadObjectName(Exception ex, SqlObjectType type)
		{
			string errorMessage = GetErrorMessage(ex);
			string objectName = string.Empty;
			if (string.IsNullOrEmpty(errorMessage))
				return objectName;
			
			if (type == SqlObjectType.UserDefinedFunction || type == SqlObjectType.View)
			{
				if (errorMessage.Contains("Invalid object name '"))
				{
					try
					{
						int patternStart = errorMessage.IndexOf("Invalid object name '", 0) + 21;
						objectName = errorMessage.Substring(patternStart, errorMessage.Length - 2 - patternStart);
					}
					catch
					{ }
				}
			}
			return objectName;
		}
		/// <summary>
		/// Determines if all scripts in a path are consistent and have drop/exist
		/// </summary>
		/// <param name="keyName"></param>
		/// <param name="catalogLogicalName"></param>
		/// <returns></returns>
        public int AssertAllScripts(string keyName, string catalogLogicalName)
		{
			bool isError = false;
            List<string> exceptions = new List<string>();
			string workingPath = GetScriptPath(catalogLogicalName, out isError);
			if (isError)
			{
				Trace.WriteLine("Logical path doesn't exist, exiting"); Trace.Flush();
				return (int)ReturnCode.FailDueSetup;
			}

			List<SqlFilePayload> objectWithDeficienciesExists = new List<SqlFilePayload>();
			List<SqlFilePayload> objectWithDeficienciesDrop = new List<SqlFilePayload>();
			List<SqlFilePayload> objects = new List<SqlFilePayload>();

            foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.Table))
			{
				if(!script.SqlScript.ToLower().Contains("exists"))
					objectWithDeficienciesExists.Add(script);
			}
            foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.StoredProcedure))
			{
				if (!script.SqlScript.ToLower().Contains("exists"))
					objectWithDeficienciesExists.Add(script);
				if (!script.SqlScript.ToLower().Contains("drop"))
					objectWithDeficienciesDrop.Add(script);
			}
            foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.UserDefinedFunction))
			{
				if (!script.SqlScript.ToLower().Contains("exists"))
					objectWithDeficienciesExists.Add(script);
				if (!script.SqlScript.ToLower().Contains("drop"))
					objectWithDeficienciesDrop.Add(script);
			}
            foreach (SqlFilePayload script in GetScriptsFromPath(workingPath, ref exceptions, SqlObjectType.View))
			{
				if (!script.SqlScript.ToLower().Contains("exists"))
					objectWithDeficienciesExists.Add(script);
				if (!script.SqlScript.ToLower().Contains("drop"))
					objectWithDeficienciesDrop.Add(script);
			}

			foreach (SqlFilePayload script in objectWithDeficienciesDrop)
			{
				objects.Add(script);
			}
			foreach (SqlFilePayload script in objectWithDeficienciesExists)
			{
				if (!objects.Contains(script))
					objects.Add(script);
			}

			foreach (SqlFilePayload script in objects)
				Trace.WriteLine(string.Format("{0} - {1}", script.FileName, script.ObjectName));

			return 0;
		}
        /// <summary>
        /// Determines the order of an object relative to a failure
        /// </summary>
        /// <param name="ex"></param>
        /// <returns></returns>
		private string DetermineBadOrderObject(Exception ex)
		{
			string errorMessage = GetErrorMessage(ex);
			if(string.IsNullOrEmpty(errorMessage))
				return string.Empty;

			string obj = string.Empty;
			try
			{
				int start = errorMessage.IndexOf("Cannot find the object ");
				if (start > 0)
				{
					start = start + 24;
					int end = errorMessage.IndexOf(@""" because");
					int length = end - start;
					obj = errorMessage.Substring(start, length);                    
				}
			}
			catch { }
			return obj;
		}
        /// <summary>
        /// Generates a script from an IScriptable object
        /// </summary>
        /// <param name="sObj"></param>
        /// <param name="options"></param>
        /// <returns></returns>
		private StringCollection ScriptWithOptions(IScriptable sObj, ScriptingOptions options)
		{
			//StringBuilder sb = new StringBuilder();
			StringCollection script = new StringCollection();
			StringCollection strings = new StringCollection();
			options.ScriptDrops = true;
			options.IncludeIfNotExists = true;
			// script DROP separately
			strings = sObj.Script(options);
			foreach (string str in strings)
			{
				script.Add(str + Environment.NewLine + "GO" + Environment.NewLine + Environment.NewLine);
				//sb.Append(str + Environment.NewLine + "GO" + Environment.NewLine + Environment.NewLine);
			}
			options.ScriptDrops = false;
			options.IncludeIfNotExists = false;
			// now script CREATE
			strings = sObj.Script(options);
			foreach (string str in strings)
			{
				script.Add(str + Environment.NewLine + "GO" + Environment.NewLine + Environment.NewLine);
				//sb.Append(str + Environment.NewLine + "GO" + Environment.NewLine + Environment.NewLine);
			}

			return script;
		}
		/// <summary>
		/// Good times
		/// </summary>
		/// <param name="scriptingWithNoExistsOptions"></param>
		/// <returns></returns>
		private StringCollection ScriptObject(SqlSmoObject smoObject, ScriptingOptions scriptingOptionSet)
		{
			if (smoObject is Schema)
				return ScriptWithOptions(((Schema)smoObject),scriptingOptionSet);
			if (smoObject is Synonym)
				return ScriptWithOptions(((Synonym)smoObject), scriptingOptionSet);
			if (smoObject is Table)
				return ((Table)smoObject).Script(scriptingOptionSet);
			if (smoObject is ForeignKey)
				return ((ForeignKey)smoObject).Script(scriptingOptionSet);
            if (smoObject is Index)
                return ((Index)smoObject).Script(scriptingOptionSet);
			if (smoObject is View)
				return ScriptWithOptions(((View)smoObject), scriptingOptionSet);
			if (smoObject is UserDefinedFunction)
				return ScriptWithOptions(((UserDefinedFunction)smoObject), scriptingOptionSet);
            if (smoObject is UserDefinedTableType)
                return ScriptWithOptions(((UserDefinedTableType)smoObject), scriptingOptionSet);
            if (smoObject is UserDefinedType)
                return ScriptWithOptions(((UserDefinedType)smoObject), scriptingOptionSet);
			if (smoObject is StoredProcedure)
				return ScriptWithOptions(((StoredProcedure)smoObject), scriptingOptionSet);
            if (smoObject is Trigger)
                return ScriptWithOptions(((Trigger)smoObject), scriptingOptionSet);

			return new StringCollection();
		}
		/// <summary>
		/// Retrieve script definitions of a specific object type from a path 
		/// </summary>
		/// <param name="workingPath"></param>
		/// <param name="objType"></param>
		/// <returns></returns>
		public static List<SqlFilePayload> GetScriptsFromPath(string workingPath, ref List<string> exceptions, SqlObjectType objType)
		{
			string objPath = System.IO.Path.Combine(workingPath, objType.ToString());
            string endsWith = string.Format(".{0}.sql", objType.ToString());

			//sanity on path
			if (!System.IO.Directory.Exists(objPath))
				return new List<SqlFilePayload>();

			List<SqlFilePayload> scriptCollection = new List<SqlFilePayload>();

			//deserialize from file
            foreach (string file in System.IO.Directory.GetFiles(objPath, "*.sql", SearchOption.TopDirectoryOnly))
			{                
				//Trace.WriteLine(string.Format("{1} running script {0} ",System.IO.Path.GetFileName(file), objType.ToString()));
                if(!file.EndsWith(endsWith, true, System.Globalization.CultureInfo.CurrentCulture) && !objType.ToString().Equals("SetupScript", StringComparison.CurrentCultureIgnoreCase))
                {
                    string msg = string.Format("Failed script {0} of type {1} does not end with {2} and will be skipped", file, objType.ToString(), endsWith);
                    Trace.WriteLine(msg);
                    exceptions.Add(msg);
                    continue;
                }
				scriptCollection.Add(SqlFilePayload.Create(System.IO.Path.GetFileName(file), System.IO.File.ReadAllText(file), objType));
			}

			return scriptCollection;
		}
        /// <summary>
        /// Retrieve script definitions of a specific object type from a path 
        /// </summary>
        /// <param name="workingPath"></param>
        /// <param name="objType"></param>
        /// <returns></returns>
        public static List<SqlFilePayload> GetScriptsFromPath(string objPath)
        {
            //sanity on path
            if (!System.IO.Directory.Exists(objPath))
                return new List<SqlFilePayload>();

            List<SqlFilePayload> scriptCollection = new List<SqlFilePayload>();

            //deserialize from file
            foreach (string file in System.IO.Directory.GetFiles(objPath, "*.sql", SearchOption.TopDirectoryOnly))
            {
                scriptCollection.Add(SqlFilePayload.Create(System.IO.Path.GetFileName(file), System.IO.File.ReadAllText(file), SqlObjectType.None));
            }

            return scriptCollection;
        }
		/// <summary>
		/// Finds a script from file
		/// </summary>
		/// <param name="workingPath"></param>
		/// <param name="objType"></param>
		/// <param name="fileName"></param>
		/// <returns></returns>
		public static SqlFilePayload GetScriptFromPath(string workingPath, string fileName)
		{
			SqlObjectType[] types = new SqlObjectType[] { SqlObjectType.UserDefinedFunction, SqlObjectType.View };
			foreach (SqlObjectType objType in types)
			{
				string objPath = System.IO.Path.Combine(workingPath, objType.ToString());

				//sanity on path
				if (!System.IO.Directory.Exists(objPath))
					return null;

				//deserialize from file
				string sqlFileName = string.Format("{0}.{1}.sql", fileName, objType.ToString());
				foreach (string file in System.IO.Directory.GetFiles(objPath))
				{
					if(Path.GetFileName(file).ToLower() == sqlFileName.ToLower())
						return SqlFilePayload.Create(System.IO.Path.GetFileName(file), System.IO.File.ReadAllText(file), objType);
				}
			}

			return null;
		}
        /// <summary>
        /// Pokes through the job stream to search for a file
        /// </summary>
        /// <param name="workingPath"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        private static string CheckJobScriptPath(string workingPath, string fileName)
        {
            //bool x = (fileName.Contains("TBL.IDX_Group.Index"));

            //sanity parameters
            if (string.IsNullOrEmpty(workingPath) || string.IsNullOrEmpty(fileName))
                return string.Empty;
            
            //sanity path
            string objPath = System.IO.Path.Combine(workingPath, "Job");
            if (!Directory.Exists(objPath))
                return string.Empty;

            return System.IO.Directory.GetFiles(objPath, string.Format("*{0}*",fileName), SearchOption.AllDirectories).FirstOrDefault();            
        }
		/// <summary>
		/// Retrieves the working logical catalog path
		/// </summary>
		/// <param name="catalogLogicalName"></param>
		/// <param name="isError"></param>
		/// <returns></returns>
		private string GetScriptPath(string catalogLogicalName, out bool isError)
		{
			isError = true;

			string rootPath = System.IO.Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetModules().First().FullyQualifiedName), "Database");
			if (!System.IO.Directory.Exists(rootPath))
			{
				return string.Empty;
			}

			string trunkPath = System.IO.Path.Combine(rootPath, catalogLogicalName);
			if (!System.IO.Directory.Exists(trunkPath))
			{
				return string.Empty;
			}

			isError = false;
			return trunkPath;
		}
		/// <summary>
		/// Drops all user-defined objects in a catalog
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="db"></param>
		private void DropCatalogObjects(SqlConnection conn, Database db, bool noDropTables)
		{
			//remove all referential integrity
			if (!noDropTables)
			{
				foreach (Table tbl in db.Tables.Cast<Table>().ToList())
				{
					if (!tbl.IsSystemObject)
					{
						foreach (ForeignKey fk in tbl.ForeignKeys.Cast<ForeignKey>().ToList())
						{
							Trace.WriteLine(string.Format("Object FK {0} Dropped", fk.Name));
							fk.Drop();
						}
                        foreach (Trigger tgr in tbl.Triggers.Cast<Trigger>().ToList())
                        {
                            Trace.WriteLine(string.Format("Object TGR {0} Dropped", tgr.Name));
                            tgr.Drop();
                        }
					}
				}
			}
			foreach (StoredProcedure sproc in db.StoredProcedures.Cast<StoredProcedure>().ToList())
			{
				if (!sproc.IsSystemObject)
				{
					Trace.WriteLine(string.Format("Object StoredProcedure {0} Dropped", sproc.Name));
					sproc.Drop();
				}
			}
			foreach (View view in db.Views.Cast<View>().ToList())
			{
				if (!view.IsSystemObject)
				{
					Trace.WriteLine(string.Format("Object View {0} Dropped", view.Name));
					view.Drop();
				}
			}
			foreach (UserDefinedFunction func in db.UserDefinedFunctions.Cast<UserDefinedFunction>().ToList())
			{
				if (!func.IsSystemObject)
				{
					Trace.WriteLine(string.Format("Object UserDefinedFunction {0} Dropped", func.Name));
					func.Drop();
				}
			}
            foreach (UserDefinedTableType func in db.UserDefinedTableTypes.Cast<UserDefinedTableType>().ToList())
            {
                if (func.IsUserDefined)
                {
                    Trace.WriteLine(string.Format("Object UserDefinedTableType {0} Dropped", func.Name));
                    func.Drop();
                }
            }
            foreach (UserDefinedDataType func in db.UserDefinedTableTypes.Cast<UserDefinedDataType>().ToList())
            {
                Trace.WriteLine(string.Format("Object UserDefinedDataType {0} Dropped", func.Name));
                func.Drop();
            }
            foreach (UserDefinedType func in db.UserDefinedTableTypes.Cast<UserDefinedType>().ToList())
            {
                Trace.WriteLine(string.Format("Object UserDefinedType {0} Dropped", func.Name));
                func.Drop();
            }

			DropSynonyms(conn, db);

			if (!noDropTables)
			{
				foreach (Table tbl in db.Tables.Cast<Table>().ToList())
				{
					if (!tbl.IsSystemObject)
					{
						Trace.WriteLine(string.Format("Object Table {0} Dropped", tbl.Name));
						tbl.Drop();
					}
				}
			}
		}
		/// <summary>
		/// Default scripting options for serialization
		/// </summary>
		/// <returns></returns>
		private ScriptingOptions GetScriptingOptions(bool isFkCall, bool ifNotExists, bool allIndexes, bool isClusteredOnly)
		{
			ScriptingOptions so = new ScriptingOptions();

			so.AllowSystemObjects = false;
			so.AnsiFile = false;
			so.AnsiPadding = true;
			so.IncludeHeaders = false;
            
            if (allIndexes)
            {
                so.Indexes = true;
            }
            else
            {
                so.ClusteredIndexes = false;
                so.NonClusteredIndexes = false;
            }

            if (isClusteredOnly)
            {
                so.ClusteredIndexes = true;
                so.NonClusteredIndexes = false;
            }

			//so.DriAllConstraints = true;
			so.DriChecks = true;
			so.DriDefaults = true;
			so.DriPrimaryKey = (isClusteredOnly || allIndexes);
            so.DriUniqueKeys = (isClusteredOnly || allIndexes);//!isFkCall; //object definition, bring across unique keys/indexes into actual definition

			so.DriWithNoCheck = true;
			so.DriForeignKeys = isFkCall;
			so.EnforceScriptingOptions = isFkCall;
			so.SchemaQualifyForeignKeysReferences = isFkCall;

			so.Triggers = true;

			so.ExtendedProperties = true;
			so.IncludeDatabaseContext = false;
			so.IncludeIfNotExists = ifNotExists;
			so.NoCollation = true;

			return so;
		}
		/// <summary>
		/// Drop synonyms using built-ins rather than SMO functionality (SMO is broken)
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="db"></param>
		private void DropSynonyms(SqlConnection conn, Database db)
		{
			List<string> collection = new List<string>();

			using (conn)
			{
				if (conn.State != System.Data.ConnectionState.Open)
					conn.Open();

				SqlCommand cmd = new SqlCommand(sqlSynonym, conn);
				SqlDataReader reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					try
					{
						collection.Add(reader.GetString(7));
					}
					catch (InvalidCastException c)
					{
						System.Diagnostics.Trace.WriteLine(string.Format("Error:{0}", c.Message));
						//logging out
					}
				}
				reader.Close();

				foreach (string dropSyn in collection)
				{
					Trace.WriteLine(string.Format("Object Synonym {0} Dropped", dropSyn));
					cmd = new SqlCommand(dropSyn, conn);

					try { cmd.ExecuteNonQuery(); }
					catch (Exception ex) { Trace.WriteLine(string.Format("Object Synonym Failed Dropp:{0}", ex.Message)); }
				}
			}
		}
		/// <summary>
		/// Retrieves synonyms using built-ins rather than SMO functionality (SMO is broken)
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="db"></param>
		/// <returns></returns>
		private List<Synonym> GetSynonyms(SqlConnection conn, Database db)
		{
			List<Synonym> collection = new List<Synonym>();

			using (conn)
			{
				if (conn.State != System.Data.ConnectionState.Open)
					conn.Open();

				SqlCommand cmd = new SqlCommand(sqlSynonym, conn);
				SqlDataReader reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					try
					{
						Synonym tmpSyn = new Synonym(db, reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5));
						collection.Add(tmpSyn);
					}
					catch (InvalidCastException c)
					{
						System.Diagnostics.Trace.WriteLine(string.Format("Error:{0}", c.Message));
						//logging out
					}
				}



				reader.Close();
			}
			return collection;
		}
		/// <summary>
		/// Serliazes an object from a catalog to a file
		/// </summary>
		/// <param name="script"></param>
		/// <param name="catalogLogicalName"></param>
		/// <param name="objSchema"></param>
		/// <param name="objName"></param>
		/// <param name="objType"></param>
		/// <returns></returns>
		internal static string SerializeToFile(StringCollection script, string catalogLogicalName, string objSchema, string objName, SqlObjectType objType)
		{
			//export base path
			string baseCatalogPath = SetupPath(catalogLogicalName);
			string leafCatalogPath = baseCatalogPath;

			//export path by object
			switch (objType)
			{
				case SqlObjectType.Table:
					leafCatalogPath = System.IO.Path.Combine(baseCatalogPath, SqlObjectType.Table.ToString());
					break;
				case SqlObjectType.Schema:
					leafCatalogPath = System.IO.Path.Combine(baseCatalogPath, SqlObjectType.Schema.ToString());
					break;
				case SqlObjectType.Synonym:
					leafCatalogPath = System.IO.Path.Combine(baseCatalogPath, SqlObjectType.Synonym.ToString());
					break;
                case SqlObjectType.UserType:
                    leafCatalogPath = System.IO.Path.Combine(baseCatalogPath, SqlObjectType.UserType.ToString());
                    break;
                case SqlObjectType.UserTypeTable:
                    leafCatalogPath = System.IO.Path.Combine(baseCatalogPath, SqlObjectType.UserTypeTable.ToString());
                    break;
				case SqlObjectType.UserDefinedFunction:
					leafCatalogPath = System.IO.Path.Combine(baseCatalogPath, SqlObjectType.UserDefinedFunction.ToString());
					break;
				case SqlObjectType.StoredProcedure:
					leafCatalogPath = System.IO.Path.Combine(baseCatalogPath, SqlObjectType.StoredProcedure.ToString());
					break;
				case SqlObjectType.ForeignKey:
					leafCatalogPath = System.IO.Path.Combine(baseCatalogPath, SqlObjectType.ForeignKey.ToString());
					break;
				case SqlObjectType.View:
					leafCatalogPath = System.IO.Path.Combine(baseCatalogPath, SqlObjectType.View.ToString());
					break;
                case SqlObjectType.Index:
                    leafCatalogPath = System.IO.Path.Combine(baseCatalogPath, SqlObjectType.Index.ToString());
                    break;
                case SqlObjectType.ClusteredIndex:
                    leafCatalogPath = System.IO.Path.Combine(baseCatalogPath, SqlObjectType.ClusteredIndex.ToString());
                    break;
                case SqlObjectType.Trigger:
                    leafCatalogPath = System.IO.Path.Combine(baseCatalogPath, SqlObjectType.Trigger.ToString());
                    break;
				default:
					break;
			}

            foreach(char invalidChar in Path.GetInvalidFileNameChars())
                objName = objName.Replace(Convert.ToString(invalidChar), "");

			//create file
			string fileName = string.IsNullOrEmpty(objSchema) ?
					string.Format("{0}.{1}.sql", objName, objType.ToString()) :
					string.Format("{0}.{1}.{2}.sql", objSchema, objName, objType.ToString());

            string fileNamePath = System.IO.Path.Combine(leafCatalogPath, fileName);
            //does it exist already elsewhere?
            if (objType == SqlObjectType.Index)
            {
                //find it existed elsewhere?
                string otherPath = CheckJobScriptPath(baseCatalogPath, fileName);
                if (!string.IsNullOrEmpty(otherPath))
                {
                    fileNamePath = otherPath;
                }
            }
            
			//remove previous file, existing
			if (System.IO.File.Exists(fileNamePath))
				System.IO.File.Delete(fileNamePath);

			//create file
			try
			{
				System.IO.StreamWriter streamWriter = System.IO.File.CreateText(fileNamePath);

				foreach (string line in script)
				{
					streamWriter.WriteLine(line);
				}

				//close file
				streamWriter.Flush();
				streamWriter.Close();
			}
			catch (Exception ex) 
			{ Trace.WriteLine(string.Format("Couldn't create file {0} due to: {1}", fileName, ex.Message)); }
			//serliaze to file

			return fileNamePath;
		}
		/// <summary>
		/// Builds the logical catalog serialization tree in a path
		/// </summary>
		/// <param name="catalogLogicalName"></param>
		/// <returns></returns>
		internal static string SetupPath(string catalogLogicalName)
		{
			//default output trunk
			string basePath = System.IO.Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetModules().First().FullyQualifiedName), "Database");

			if (!System.IO.Directory.Exists(basePath))
				System.IO.Directory.CreateDirectory(basePath);

			//default output branch
			string catalogPath = System.IO.Path.Combine(basePath, catalogLogicalName);

			if (!System.IO.Directory.Exists(catalogPath))
				System.IO.Directory.CreateDirectory(catalogPath);

			//default output leafs 
			string[] folders = Enum.GetNames(typeof(SqlObjectType));

			foreach (string folder in folders.Where<string>(p => p.ToUpper() != "NONE"))
			{
				string leafPath = System.IO.Path.Combine(catalogPath, folder);
				if (!System.IO.Directory.Exists(leafPath))
					System.IO.Directory.CreateDirectory(leafPath);
			}

			return catalogPath;
		}
		/// <summary>
		/// Recursive drill down into the exception, for the bottom-most message
		/// </summary>
		/// <param name="ex"></param>
		/// <returns></returns>
		internal static string GetErrorMessage(Exception ex)
		{
			if (ex != null && ex.InnerException != null)
				return GetErrorMessage(ex.InnerException);
			else
				return ex.Message;
		}
		#region special synonym script
		string sqlSynonym = @"SELECT syn.name, sc.name,
								COALESCE(PARSENAME(base_object_name,4),@@servername) AS serverName, 
								COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName, 
								COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName, 
								PARSENAME(base_object_name,1) AS objectName,
								'IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = ''' + syn.name + ''')
								CREATE SYNONYM ['+sc.name+'].['+syn.name+'] FOR ['+COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID()))+'].['+COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID()))+'].['+PARSENAME(base_object_name,1)+']',
								'IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N'''+syn.name+''')
								DROP SYNONYM ['+sc.name+'].['+syn.name+']'

							FROM sys.synonyms syn inner join sys.schemas sc
								on syn.schema_id = sc.schema_id
							ORDER BY serverName,dbName,schemaName,objectName";
		#endregion
		/// <summary>
		/// On bad parameters
		/// </summary>
		internal static void InvalidArgs()
		{
			Trace.WriteLine("Invalid arguments supplied");
			Trace.Flush();
		}
    }
	/// <summary>
	/// Container for the sql file and payload handler
	/// </summary>
	public class SqlFilePayload
	{
		/// <summary>
		/// Rank of the object
		/// </summary>
		public short ObjectRank
		{
			get
			{
				if (!string.IsNullOrEmpty(ObjectName))
				{
                    //rank the object by schema
                    return 1;
				}
				return 10;
			}
		}
        /// <summary>
        /// Returns a rank of the constraint name for nice ordering
        /// </summary>
        public short NameRank
        {
            get
            {
                if (!string.IsNullOrEmpty(ConstraintName))
                {
                    //you can rank objects for sorting here
                    return 10;
                }
                return 1;
            }
        }
		/// <summary>
		/// Schema, for all non-schema objects
		/// </summary>
		public string SchemaName
		{
			get
			{
				string schema = string.Empty;
                if (!string.IsNullOrEmpty(ObjectName))
                {
                    try { schema = ObjectName.Substring(0, ObjectName.IndexOf(".")); }
                    catch { }
                }
				return schema;
			}
		}
		/// <summary>
		/// Should the be name of the object
		/// </summary>
		public string ObjectName
		{
			get
			{
                switch (ObjectType)
                {
                    case SqlObjectType.Index:
                        {
                            if (string.IsNullOrEmpty(SqlScript))
                                return null;

                            //"ON [Entity].[Class] ("
                            int padOut = SqlScript.IndexOf("CREATED ON") + 30;
                            int start = SqlScript.IndexOf(" ON ", padOut) + 4;
                            int end = SqlScript.IndexOf("(", start);

                            try
                            {
                                string scopeName = SqlScript.Substring(start, (end - start) - 1).Trim();
                                if (scopeName.Contains('['))
                                    scopeName = scopeName.Replace("[", "").Replace("]", "");
                                return scopeName;
                            }
                            catch
                            { }
                            return null;
                        } 
                    //everything else
                    default:
                        {
                            if (!string.IsNullOrEmpty(FileName))
                            {
                                string obj = string.Empty;
                                try { obj = FileName.Substring(0, FileName.IndexOf(".", FileName.IndexOf(".") + 1)); } //get from second classifier, skip schema
                                catch { }
                                return obj;
                            }
                            else
                                return null;
                        }
                }
			}
		}
		/// <summary>
		/// Name of the object
		/// </summary>
		public string TableName
		{
            get
            {
                if (!string.IsNullOrEmpty(ObjectName))
                {
                    string obj = string.Empty;
                    try
                    {
                        if (ObjectName.Contains('.'))
                            obj = ObjectName.Substring(ObjectName.IndexOf(".") + 1, ObjectName.Length - ObjectName.IndexOf(".") - 1);
                    }
                    catch { }
                    return obj;
                }
                else
                    return null;
            }
		}
		/// <summary>
		/// Name of sql filestream
		/// </summary>
		public string FileName { get; set; }
		/// <summary>
		/// Contents of sql filestream
		/// </summary>
		public string SqlScript { get; set; }
        public string ConstraintName
        {
            get
            {
                switch (ObjectType)
                {
                    case SqlObjectType.Index:
                        {
                            if (string.IsNullOrEmpty(SqlScript))
                                return null;

                            //"ON [Entity].[Class] ("
                            int padOut = SqlScript.IndexOf("CREATED ON") + 30;
                            int start = SqlScript.IndexOf(" INDEX ", padOut) + 7;
                            int end = SqlScript.IndexOf(" ON", start);

                            try
                            {
                                string scopeName = SqlScript.Substring(start, (end - start) - 1).Trim();
                                if (scopeName.Contains('['))
                                    scopeName = scopeName.Replace("[", "").Replace("]", "");
                                return scopeName;
                            }
                            catch
                            { }
                            return null;
                        }
                    default:
                        return null;
                }
            }
        }
        /// <summary>
        /// Type of object this script represents
        /// </summary>
        public SqlObjectType ObjectType { get; set; }
        public void CleanUp()
        {
            if (!string.IsNullOrEmpty(SqlScript))
            {
                if (SqlScript.Contains("USE "))
                {
                    string tmp = SqlScript;
                    int startPosition = tmp.IndexOf("USE ");
                    int endPosition = tmp.IndexOf("GO", startPosition);
                    SqlScript = tmp.Substring(endPosition);
                }
            }
        }
		/// <summary>
		/// Constructor for handler
		/// </summary>
		/// <param name="fileName"></param>
		/// <param name="script"></param>
		/// <returns></returns>
		public static SqlFilePayload Create(string fileName, string script, SqlObjectType objType)
		{
			SqlFilePayload file = new SqlFilePayload();
			file.FileName = fileName;
			file.SqlScript = script;
            file.ObjectType = objType;
			return file;
		}
		public override string ToString()
		{
			if (!string.IsNullOrEmpty(FileName))
				return FileName;
			else
				return base.ToString();
		}
	}
    /// <summary>
    /// Used to determine the ordering of nesting within view
    /// </summary>
	struct SqlViewHierarchy
	{
		public List<SqlViewHierarchy> Children { get; set; }
		public string Name { get; set; }
		public static SqlViewHierarchy Create(string viewNameFullQualified)
		{
			SqlViewHierarchy view = new SqlViewHierarchy();
			view.Name = viewNameFullQualified;
			return view;
		}
	}
}
